package com.wms.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Locale;
import java.util.Map;

import android.annotation.TargetApi;
import android.os.Build;
import android.os.StrictMode;
import android.util.Log;

import com.wms.assets.Categories;
import com.wms.assets.Item;
import com.wms.assets.SearchCriteria;
import com.wms.helper.Database;

@TargetApi(Build.VERSION_CODES.GINGERBREAD)
public class ItemsModel 
{
	private static final String TAG = "WMS";
	private static ItemsModel instance = null;
	private ArrayList<Item> items;
	
	/**
	 * Model for Items, both lost and found
	 */
	private ItemsModel() {
		items = new ArrayList<Item>();
		StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
		StrictMode.setThreadPolicy(policy);
	}
	
	/**
	 * Returns the global instance of this class.
	 * @return an instance of ItemsModel
	 */
	public static ItemsModel getInstance() {
		if (instance == null) {
			instance = new ItemsModel();
		}
		return instance;
	}
	
	/**
	 * Adds a new item to the DB
	 * 
	 * TODO: consider abstracting this out to a commit method 
	 * @param item the item to be added
	 * @throws ClassNotFoundEtxception 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	public void addItem(Item item) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		Connection conn = Database.getWriteConnection();		
		
		PreparedStatement statement = conn.prepareStatement("INSERT INTO Item (name,status,owner,description, Categories, ACCESSORIES, ANIMALS, CLOTHING, ELECTRONICS, TOYS, MISCELLANY, Location) VALUES (?,?,?,?,?, ?,?,?,?,?,?,?)");
		statement.setString(1, item.getName());
		statement.setString(2, item.getStatus());
		statement.setString(3, item.getOwner());
		statement.setString(4, item.getDescription());
		statement.setString(5, item.getCategoriesString());
		statement.setBoolean(6, item.isCategory(Categories.ACCESSORIES));
		statement.setBoolean(7, item.isCategory(Categories.ANIMALS));
		statement.setBoolean(8, item.isCategory(Categories.CLOTHING));
		statement.setBoolean(9, item.isCategory(Categories.ELECTRONICS));
		statement.setBoolean(10, item.isCategory(Categories.TOYS));
		statement.setBoolean(11, item.isCategory(Categories.MISCELLANY));
		statement.setString(12, item.getLocation());
		
		
		int result = statement.executeUpdate();
		
		Log.v(TAG, "Inserted " + result + " row(s).");
		statement.close();
		conn.close();
	
		items.add(item);
	}
	
	/**
	 * Updates and returns the items on the database
	 * @return ArrayList<Item> of all unfiltered Items on database
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	public ArrayList<Item> getItems() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		updateItemsFromDataSource();
		return items;
	}
	
	private Item getItem(ResultSet result) throws SQLException{
		Item item = new Item(result.getString("name"), result.getBoolean("CLOTHING"), result.getBoolean("ACCESSORIES"), result.getBoolean("TOYS"), result.getBoolean("ELECTRONICS"), result.getBoolean("ANIMALS"), result.getBoolean("MISCELLANY"));
		item.description = result.getString("description");
		item.status = result.getString("status");
		item.setOwner(result.getString("owner"));
		item.setCreated(result.getTimestamp("created"));
		item.setLocation(result.getString("Location"));
		return item;
	}
	
	/**
	 * 
	 * @param names - name field of the item
	 * @param cat - categories
	 * @param loc - location field
	 * @return ArrayList of items filtered based on the parameters
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	public ArrayList<Item> getFilteredItems(String names, String cat, String loc) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		ArrayList<Item> items = null;
		items = getItems();
		
		ArrayList<Item> filtered = new ArrayList<Item>();
		for (Item item : items) {
			if (item.name.toLowerCase(Locale.US).contains(names.toLowerCase(Locale.US)) || names.equals("")) {
				if (item.isCategory(cat) || cat.equals(Categories.ANY)) {
					if (item.getLocation().toLowerCase(Locale.US).contains(loc.toLowerCase(Locale.US)) || loc.equals(""))
						filtered.add(item);
				}
			}
		}
		return filtered;
	}
	/**
	 * Sets the internal items array to whatever is in the database
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	private void updateItemsFromDataSource() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		
		Connection conn = Database.getReadConnection();
		PreparedStatement statement = conn.prepareStatement("SELECT * FROM Item");
		ResultSet result = statement.executeQuery();	
	
		items.clear();
		
		while (result.next()) {
			items.add(getItem(result));
		}
		
		statement.close();
		conn.close();
	}
	
	/**
	 * Executes a SQL query to get items from database, based on the SearchCriteria
	 * @param criteria - SearchCriteria that contains the filters
	 * @return searchItems - ArrayList of items found using the search criteria
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 */
	public ArrayList<Item> searchItem(SearchCriteria sc) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException{
		ArrayList<Item> searchitems = new ArrayList<Item>();
		Connection conn = Database.getReadConnection();
		
		String query = "SELECT * FROM Item ";
		Map<String, String> criteria = sc.getCriterias();
		if (!criteria.isEmpty()){
			query.concat("WHERE 1");
			for (String field: criteria.keySet()){
				query.concat(" AND `"+field+"` "+criteria.get(field));
			}
			Log.i(TAG, query);
		}
		
		PreparedStatement statement = conn.prepareStatement(query);
		ResultSet result = statement.executeQuery();	
			
		while (result.next()) {
			searchitems.add(getItem(result));
		}
		
		statement.close();
		conn.close();
		return searchitems;
	}
	
}
